package com.rykj.qxj.test.sql;

import java.io.ByteArrayOutputStream;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.swing.plaf.FontUIResource;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.util.TablesNamesFinder;

public class SqlCreateApi {
	
	public static String judge_type(String sql) {
		 
		try {
			Statement statement = (Statement) CCJSqlParserUtil.parse(sql);
			System.out.println(statement);
			List<String> test_select_table = test_select_table(statement.toString());
			System.out.println(test_select_table);
		} catch (JSQLParserException e) {
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			e.printStackTrace(new PrintStream(baos));
			String exception = baos.toString();
			/*String regEx = "Encountered(.*)";
			Pattern pat = Pattern.compile(regEx);
			Matcher mat = pat.matcher(exception);
			while (mat.find()) {
				exception = mat.group(1);
			}
			// System.out.println(exception);
			String line = "";
			String regEx2 = "line (.*),";
			pat = Pattern.compile(regEx2);
			mat = pat.matcher(exception);
			while (mat.find()) {
				line = mat.group(1);
			}*/
			// System.out.println(line);
 
			/*int line_num = Integer.valueOf(line).intValue();
			int indexofcolumn = exception.indexOf("column");
			String errornumber = exception.substring(indexofcolumn + 7,
					exception.length() - 1);
			int error_num = Integer.valueOf(errornumber).intValue();
			System.out.println(error_num);
 
			String ERROR_location = "";
			if (error_num != 1) {
				String sql_sub = sql.substring(0, error_num - 2); // 发生错误位置前面的字符串
				// 错误信息单词往往处于错误位置的前一个地方单词
				// 获取错误位置两个前面两个空格之间的单词，并保存
				sql_sub = new StringBuilder(sql_sub).reverse().toString();
				int indexofspace = sql_sub.indexOf(" ");
				String sql_error = sql_sub.substring(0, indexofspace);
				sql_error = new StringBuilder(sql_error).reverse().toString();
				ERROR_location = "\"" + sql_error + "\"" + " at line "
						+ line_num + " at column " + error_num;
			} else {
				int indexofspace = sql.indexOf(" ");
				String sql_error = sql.substring(0, indexofspace);
				ERROR_location = "\"" + sql_error + "\"" + " at line "
						+ line_num + " at column " + error_num;
			}*/
			return ""; // 错误信息的返回
		}
		String result = "correct";
		return result; // Jsql可以解析，返回correct
	}
	
	static final List<String> getWhereName(String query) {
		List<String> nameList=new ArrayList<String>();
		int length = query.length();
		for (int i = 0; i < length; i++) {
			char c = query.charAt(i);
				 if (c == ':' && i + 1 < length && Character.isJavaIdentifierStart(query.charAt(i + 1))) {
					int j = i + 2;
					while (j < length && Character.isJavaIdentifierPart(query.charAt(j))) {
						j++;
					}
					String name = query.substring(i + 1, j);
					nameList.add(name);
				}
		}
		return nameList;
	}
	static final List<String> getpenName(String query) {
		List<String> nameList=new ArrayList<String>();
		int length = query.length();
		boolean inSingleQuote = false;
		boolean inDoubleQuote = false;
		for (int i = 0; i < length; i++) {
			char c = query.charAt(i);
			if (inSingleQuote) {
				if (c == '\'') {
					inSingleQuote = false;
				}
			} else if (inDoubleQuote) {
				if (c == '"') {
					inDoubleQuote = false;
				}
			} else {
				if (c == '\'') {
					inSingleQuote = true;
				} else if (c == '"') {
					inDoubleQuote = true;
				} else if (c == ':' && i + 1 < length && Character.isJavaIdentifierStart(query.charAt(i + 1))) {
					int j = i + 2;
					while (j < length && Character.isJavaIdentifierPart(query.charAt(j))) {
						j++;
					}
					String name = query.substring(i + 1, j);
					nameList.add(name);
					
				}
			}
		}
		return nameList;
	}
	
	public static List<String> test_select_table(String sql)
			throws JSQLParserException {
		Statement statement = (Statement) CCJSqlParserUtil.parse(sql);
		Select selectStatement = (Select) statement;
		TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
		List<String> tableList = tablesNamesFinder
				.getTableList(selectStatement);
		return tableList;
	}

	
	public static void main(String[] args) throws JSQLParserException {
		String sqlStr="select s.`name` as dd,s.age as aa FROM student s INNER JOIN grade g ON s.grade_id=g.id";		
		/*String judge_type = judge_type(sqlStr);
		System.out.println(judge_type);
		List<String> whereName = getWhereName(sqlStr);
		System.out.println(whereName);
		String sql = "select a as A,b as B,c as C from d,e";
	    String[] strs = Pattern.compile("([^,]+\\s+as\\s+|\\s+from)").split(sqlStr);
	    for(String s : strs){
	        if(!s.equals(""))
	            System.out.println(s.replaceAll(",", ""));
	    }*/
		
		//String subString = subString(sqlStr.toLowerCase(),"select","from");
		//System.out.println(subString);
		/*String sql="SELECT test1.name AS name FROM test1 LEFT JOIN test2 ON test1.id = test2.id WHERE test1.name = :name;";
		String sql1="SELECT test1.name AS name FROM test1 LEFT JOIN test2 ON test1.id = test2.id WHERE test1.name = ':name';";
		String doubbleSql="select * from api_manage am WHERE am.`name` LIKE  \"%':name'%\" and am.age=:age and am.address=':address'";
		String replace = sql.replace("\'", "");
		System.out.println("原来没有单引号的语句替换之后==="+replace);
		String replace1 = sql1.replace("\'", "");
		System.out.println("原来有单引号的语句替换之后==="+replace1);
		Statement statement1 = (Statement) CCJSqlParserUtil.parse(sql);
		System.out.println("sql语句解析器1:==="+statement1);
		
		Statement statement2 = (Statement) CCJSqlParserUtil.parse(sql1);
		System.out.println("sql语句解析器2:==="+statement2);
		System.out.println("sql语句解析器2:==="+statement2);
		
		
		System.out.println("原来没有单引号的语句替换之后==="+doubbleSql);
		String replace3 = doubbleSql.replace("\'", "");
		System.out.println("原来有单引号的语句替换之后==="+replace3);
		Statement statement3 = (Statement) CCJSqlParserUtil.parse(replace3);
		System.out.println("sql语句解析器3:==="+statement3);
		
		
		List<String> whereName = getWhereName(statement3.toString());
		System.out.println(whereName);
		List<String> responseParamBySql = getResponseParamBySql(statement3.toString());
		System.out.println(responseParamBySql);
		
		String testSql="select t_comment.comment as comment from t_post left join t_comment on t_post.id = t_comment.post_id";
		//String replaceTest = testSql.replace("\'", "");
		System.out.println("原来有单引号的语句替换之后==="+testSql);
		Statement statementTest = (Statement) CCJSqlParserUtil.parse(testSql);
		List<String> whereNameTest = getWhereName(statementTest.toString());
		System.out.println(whereNameTest);
		
		String sqlstr = "select `a.ab1`,`b.ab2` `ab2`,a.id as `aid`,b.id as bid from tb a join td b where a.id=b.id";
		List<String> responseParamBySqlAll = getResponseParamBySqlAll(sqlstr);
		System.out.println(responseParamBySqlAll);
		
		String sqlqq="select name name,age age,address as address from api_manage am WHERE am.`name` LIKE  \"%':name'%\" and am.age=:age and am.address=':address'";
		List<String> responseParamBySqlAll2 = getResponseParamBySqlAll(sqlqq);
		System.out.println(responseParamBySqlAll2);
	    System.out.println("=============================================================");
        System.out.println("a.id as ddr".replaceAll("^([^s]*)as\\s+",""));
        System.out.println("a.id ddr".replaceAll("^([^s]*)\\s+",""));
        System.out.println("a.id".replaceAll("^([^s]*)\\.",""));
        System.out.println("address as address".replaceAll("^([^s]*)\\s+",""));
        System.out.println("===========================================================");
        
        String str="select name,age,address from t_user where name=:name and age>:age and address like \"%:address%\"";
    	List<String> res= getResponseParamBySqlAll(str);
    	System.out.println(res);*/
		String sql="select id as id, name as name, parent_id as parent from organization where id < ':id'";
		List<String> responseParamBySql1 = getResponseParamBySql1(sql);
		boolean apiResponseParamDuplicateBySql = isApiResponseParamDuplicateBySql(responseParamBySql1);
		if(apiResponseParamDuplicateBySql) {
			System.out.println("重复");
		}else {
			System.out.println("不重复");
		}
			
	} 
	
	private static List<String> getResponseParamBySql(String sql){
		    String str[] = sql.split(" "); 
		   List<String> responseName=new ArrayList<String>();
		   List<String> resultName=new ArrayList<String>();
		   for (int i=0;i<str.length;i++) {
			   if("as".equalsIgnoreCase(str[i])) {
				   responseName.add(str[i+1]);
			   }
		  }
		   for (int j=0;j< responseName.size();j++) {
			   if(responseName.get(j).contains(",")) {
				   String[] split = responseName.get(j).split(",");
				   resultName.add(split[0]);
			   }else {
				   resultName.add(responseName.get(j));
			   }
		}
		   return resultName;
	}
	
	private static List<String> getResponseParamBySqlAll(String sql){
		List<String> resultList=new ArrayList<String>();
	    String column = sql.toLowerCase().split("from")[0].trim();
	    String[] columns = column.split(",");
	    for(String str : columns){
	        str = str.trim().replaceAll("select\\s+","");
	        str = str.replaceAll("`","");
	        if(str.indexOf("as")>=0){
	           str = str.replaceAll("^([^s]*)as\\s+","");
	           resultList.add(str);
	        }
	        else if(str.indexOf(" ")>=0){
	           str = str.replaceAll("^([^s]*)\\s+","");
	           resultList.add(str);
	        }
	        else if(str.indexOf(".")>=0){
	            str = str.replaceAll("^([^s]*)\\.","");
	            resultList.add(str);
	        }else {
	        	resultList.add(str);
	        }
	        
	    }
	    return resultList;
	}
	
	
	/**
     * 截取字符串str中指定字符 strStart、strEnd之间的字符串
     * 
     * @param string
     * @param str1
     * @param str2
     * @return
     */
    public static String subString(String str, String strStart, String strEnd) {
 
        /* 找出指定的2个字符在 该字符串里面的 位置 */
        int strStartIndex = str.indexOf(strStart);
        int strEndIndex = str.indexOf(strEnd);
 
        /* index 为负数 即表示该字符串中 没有该字符 */
        if (strStartIndex < 0) {
            return "字符串 :---->" + str + "<---- 中不存在 " + strStart + ", 无法截取目标字符串";
        }
        if (strEndIndex < 0) {
            return "字符串 :---->" + str + "<---- 中不存在 " + strEnd + ", 无法截取目标字符串";
        }
        /* 开始截取 */
        String result = str.substring(strStartIndex, strEndIndex).substring(strStart.length());
        return result;
    }
    
    private static void getResponse(String sql) {
        String column = sql.toLowerCase().split("from")[0].trim();
        String[] columns = column.split(",");
        for(String str : columns){
            str = str.trim().replaceAll("select\\s+","");
            str = str.replaceAll("`","");
            if(str.indexOf("as")>=0){
               str = str.replaceAll("^([^s]*)as\\s+","");
            }
            if(str.indexOf(" ")>=0){
               str = str.replaceAll("^([^s]*)\\s+","");
            }
            if(str.indexOf(".")>=0){
                str = str.replaceAll("^([^s]*)\\.","");
            }
            System.out.println(str);
        }
       /* System.out.println("a.id as ddr".replaceAll("^([^s]*)as\\s+",""));
        System.out.println("a.id ddr".replaceAll("^([^s]*)\\s+",""));
        System.out.println("a.id".replaceAll("^([^s]*)\\.",""));*/
    }
    private static List<String> getResponseParamBySql1(String sql) {
		List<String> resultList=new ArrayList<String>();
	    String column = sql.toLowerCase().split("from")[0].trim();
	    String[] columns = column.split(",");
	    for(String str : columns){
	        str = str.trim().replaceAll("select\\s+","");
	        str = str.replaceAll("`","");
	        if(str.indexOf("as")>=0){
	           str = str.replaceAll("^([^s]*)as\\s+","");
	           resultList.add(str);
	        }else if(str.indexOf(" ")>=0){
	           str = str.replaceAll("^([^s]*)\\s+","");
	           resultList.add(str);
	        }else if(str.indexOf(".")>=0){
	            str = str.replaceAll("^([^s]*)\\.","");
	            resultList.add(str);
	        }else {
	        	resultList.add(str);
	        }
	    }
	    return resultList;
	}
    
    private static boolean isApiResponseParamDuplicateBySql(List<String> apiResponseParamList) {
		Set<String> responseList=new HashSet<String>();
		for (String string : apiResponseParamList) {
			responseList.add(string.toLowerCase());
		}
		if(responseList.size()!=apiResponseParamList.size()) {
			return true;
		}
		return false;
	}

}
